﻿Imports System.IO
Imports NPOI
Imports NPOI.HPSF
Imports NPOI.HSSF
Imports NPOI.HSSF.UserModel
Imports NPOI.POIFS
Imports NPOI.Util
Partial Class WebForm_ImportVolunteer_ExportExcel
    Inherits System.Web.UI.UserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnExport)
        ScriptManager.GetCurrent(Me.Page).RegisterPostBackControl(Me.btnExport2)

        If Not IsPostBack Then
            VolunteerQueryControl1.InitData()
        End If
    End Sub

    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
        Me.Export()


    End Sub

    Protected Sub btnExport2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport2.Click
        Me.Export2()


    End Sub

    Protected Sub gv_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
        If e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Cells(6).Text = DataBinder.Eval(e.Row.DataItem, "BirthTownName") + DataBinder.Eval(e.Row.DataItem, "BirthRegionName")
            e.Row.Cells(8).Text = DataBinder.Eval(e.Row.DataItem, "TownName") + DataBinder.Eval(e.Row.DataItem, "RegionName")
        End If
    End Sub

End Class

Partial Class WebForm_ImportVolunteer_ExportExcel
    Public GVCount As Integer
    Private Sub BindGV(ByVal gv As GridView)
        Dim dao As New BLL_ImportVolunteer_SearchRegionVolunteerControl
        Dim dt As Data.DataTable = dao.BLL_Select(VolunteerQueryControl1.OrganizationID, VolunteerQueryControl1.VOrganizationID, VolunteerQueryControl1.VolunteerId, VolunteerQueryControl1.VolunteerName, VolunteerQueryControl1.VolunteerTEL)
        GVCount = dt.Rows.Count
        gv.DataSource = dt
        gv.DataBind()
    End Sub

    Private Function GetGVData() As Data.DataTable
        'Dim dao As New BLL_ImportVolunteer_SearchRegionVolunteerControl
        'Return dao.BLL_Select(VolunteerQueryControl1.OrganizationID, VolunteerQueryControl1.VOrganizationID, VolunteerQueryControl1.VolunteerId, VolunteerQueryControl1.VolunteerName, VolunteerQueryControl1.VolunteerTEL)
        Dim sql As New MyStringBuilder
        Dim dat As New clsData
        sql.AppendFormat("SELECT distinct A.VolunteerID,A.VolunteerName ")

        sql.AppendFormat(", vb.VBookID ")
        sql.AppendFormat(", vb.IssueDate ")

        sql.AppendFormat("from VolunteerDistinctView A ")
        sql.AppendFormat("left join VBookView vb on A.VolunteerID = vb.VolunteerID and vb.Active = 'Y' ")
        sql.AppendFormat("  left JOIN (select vm.VOrganizationID,vo.VOrganizationName,vm.VolunteerID,vo.DirectUOrganizationID from VOrganizationMapping vm ")
        sql.AppendFormat("inner join VOrganizationViewV2 vo on vm.VOrganizationID=vo.VOrganizationID ) oo on A.VolunteerID=oo.VolunteerID  ")


        sql.AppendFormat("WHERE A.Active in ('2','Y') ")
        sql.AppendFormat("AND (oo.VOrganizationID = {0} or {0}=0 ) ", Me.VolunteerQueryControl1.VOrganizationID)
        If Me.VolunteerQueryControl1.OrganizationID <> "0" Then
            sql.AppendFormat("and (oo.DirectUOrganizationID = '{0}'  ) ", Me.VolunteerQueryControl1.OrganizationID)
        End If
        sql.AppendFormat("AND (A.VolunteerId = '{0}' or '{0}'='') ", Me.VolunteerQueryControl1.VolunteerId)
        sql.AppendFormat("AND (A.VolunteerName like '%{0}%' or '{0}'='') ", Me.VolunteerQueryControl1.VolunteerName)
        sql.AppendFormat("order by  A.VolunteerID ")
        ' MyObj.WriteLog(sql)
        Return dat.GetData(sql)
    End Function

    Private Sub Export()
        Dim gv As New GridView
        gv.AutoGenerateColumns = False
        gv.Page = Me.Page

        AddHandler gv.RowDataBound, AddressOf Me.gv_RowDataBound

        ' MyObj.AddBoundField(gv, "VolunteerID", "*身份證號碼")
        MyObj.AddBoundField(gv, "VolunteerName", "*姓名")
        MyObj.AddBoundField(gv, "GenderText", "*性別")
        ' MyObj.AddBoundField(gv, "Birthday", "*生日")
        MyObj.AddBoundField(gv, "MobilePhone", "*行動電話")
        MyObj.AddBoundField(gv, "OfficePhone", "*聯絡電話")
        'TryCast(gv.Columns(gv.Columns.Count - 1), BoundField).DataFormatString = "'{0}"
        TryCast(gv.Columns(gv.Columns.Count - 1), BoundField).ItemStyle.HorizontalAlign = HorizontalAlign.Left
        MyObj.AddBoundField(gv, "BirthRegionName", "戶籍-區里")
        MyObj.AddBoundField(gv, "BirthAddress", "戶籍地")
        MyObj.AddBoundField(gv, "RegionName", "通訊地址-區里")
        MyObj.AddBoundField(gv, "Address", "通訊地址")
        MyObj.AddBoundField(gv, "EmergentPerson", "緊急聯絡人")
        MyObj.AddBoundField(gv, "EmergentPhone", "緊急聯絡電話")
        MyObj.AddBoundField(gv, "EducationLevelText", "教育程度")
        MyObj.AddBoundField(gv, "OccupationTypeText", "職業類別")
        MyObj.AddBoundField(gv, "RetiredText", "是否已退休")
        MyObj.AddBoundField(gv, "VBookID", "紀錄冊號碼")
        MyObj.AddBoundField(gv, "TrainingBHour", "基礎訓時數")
        MyObj.AddBoundField(gv, "TrainingSHour", "特殊訓時數")
        MyObj.AddBoundField(gv, "TrainingWHour", "實務訓時數")

        'MyObj.AddBoundField(gv, "OccupationText", "職業")

        Me.BindGV(gv)
        If Me.GVCount > 0 Then
            MyObj.ExportToExcel(gv, "VolunteerList.xls")
        Else
            MyObj.ShowMessage("無資料")
        End If
    End Sub

    Private Sub Export2()
        'Dim gv As New GridView
        'gv.AutoGenerateColumns = False
        'gv.Page = Me.Page

        'AddHandler gv.RowDataBound, AddressOf Me.gv_RowDataBound

        'gv.Caption = String.Format("{0} 環保志工志願服務時數統計表  (時數僅能追朔至領冊日期前一年)", IIf(Me.VolunteerQueryControl1.VOrganizationID = "0", Me.VolunteerQueryControl1.OrganizationName, Me.VolunteerQueryControl1.VOrganizationName))
        'MyObj.AddBoundField(gv, "VolunteerName", "姓名")
        'MyObj.AddBoundField(gv, "VolunteerID", "身份證號碼")
        ''MyObj.AddBoundField(gv, "Birthday", "*生日")
        'MyObj.AddBoundField(gv, "OfficePhone", "*聯絡電話")
        'TryCast(gv.Columns(gv.Columns.Count - 1), BoundField).ItemStyle.HorizontalAlign = HorizontalAlign.Left
        'MyObj.AddBoundField(gv, "Address", "地址")
        'MyObj.AddBoundField(gv, "EducationLevelText", "學歷")
        'MyObj.AddBoundField(gv, "OccupationTypeText", "職業")
        'MyObj.AddBoundField(gv, "VBookID", "紀錄冊號碼")
        'MyObj.AddBoundField(gv, "IssueDate", "發冊日期")
        'MyObj.AddBoundField(gv, "_98", "98年")
        'MyObj.AddBoundField(gv, "_97", "97年")
        'MyObj.AddBoundField(gv, "_96", "96年")
        'MyObj.AddBoundField(gv, "_95", "95年")
        'MyObj.AddBoundField(gv, "_94", "94年")
        'MyObj.AddBoundField(gv, "_93", "93年")

        ''MyObj.AddBoundField(gv, "OccupationText", "職業")

        'Dim dt As Data.DataTable = Me.GetGVData()

        'dt.Columns.Add("_98")
        'dt.Columns.Add("_97")
        'dt.Columns.Add("_96")
        'dt.Columns.Add("_95")
        'dt.Columns.Add("_94")
        'dt.Columns.Add("_93")

        'dt.DefaultView.RowFilter = "VBookID is not null"
        'dt = dt.DefaultView.ToTable()

        '身分證字號遮三碼
        'For i As Integer = 0 To dt.Rows.Count - 1
        '    dt.Rows(i)("VolunteerID") = Left(dt.Rows(i)("VolunteerID").ToString(), 4) & New String("X", 3) & Right(dt.Rows(i)("VolunteerID").ToString(), 3)
        'Next

        'gv.DataSource = dt
        'gv.DataBind()

        'MyObj.ExportToExcel(gv, "VolunteerList.xls")

        Dim dt As Data.DataTable = Me.GetGVData()

        If dt.Rows.Count > 0 Then

            dt.Columns.Add("_98")
            dt.Columns.Add("_97")
            dt.Columns.Add("_96")
            dt.Columns.Add("_95")
            dt.Columns.Add("_94")
            dt.Columns.Add("_93")
            dt.DefaultView.RowFilter = "VBookID is not null"

            '身分證字號遮三碼
            For i As Integer = 0 To dt.Rows.Count - 1
                dt.Rows(i)("VolunteerID") = Left(dt.Rows(i)("VolunteerID").ToString(), 3) & New String("X", 3) & Right(dt.Rows(i)("VolunteerID").ToString(), 4)
            Next

            For i As Integer = 0 To dt.Columns.Count - 1
                dt.Columns(i).ColumnName = ChangeColumnName(dt.Columns(i).ColumnName)
            Next

            dt = dt.DefaultView.ToTable()

            Dim ms As MemoryStream = TryCast(DataTableRenderToExcel.RenderDataTableToExcelSetSheet(dt, "VolunteerList"), MemoryStream)


            ' 設定強制下載標頭。 
            Response.AddHeader("Content-Disposition", String.Format("attachment; filename=VolunteerList.xls"))
            ' 輸出檔案。 
            Response.BinaryWrite(ms.ToArray())

            ms.Close()
            ms.Dispose()
        Else
            MyObj.ShowMessage("您選擇的資料類型沒有資料可供匯出!")
        End If
    End Sub
    Protected Function ChangeColumnName(ByVal ColumnName As String) As String
        Dim ChineseColumnName As String = ""
        Select Case ColumnName
            Case "VolunteerName"
                ChineseColumnName = "姓名" & Space(2)
            Case "VolunteerID"
                ChineseColumnName = "身份證號碼"
            Case "VBookID"
                ChineseColumnName = "紀錄冊號碼" & Space(5)
            Case "IssueDate"
                ChineseColumnName = "發冊日期"
            Case "_98"
                ChineseColumnName = "98"
            Case "_97"
                ChineseColumnName = "97"
            Case "_96"
                ChineseColumnName = "96"
            Case "_95"
                ChineseColumnName = "95"
            Case "_94"
                ChineseColumnName = "94"
            Case "_93"
                ChineseColumnName = "93"

        End Select
        Return ChineseColumnName
    End Function

End Class